﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using QuanLyGiaoVuService.ReportServices;
using System.IO;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuService.Domain;
using System.Reflection;
using System.Xml;
using System.Text.RegularExpressions;

namespace QuanLyGiaoVuService.CommonServices
{
    public class ExcelService : IExcelService
    {
        private ILopMonHocReportService _lopMonHocReportService;
        private IThamSoService _thamSoService;

        public ExcelService(ILopMonHocReportService lopMonHocReportService, IThamSoService thamSoService)
        {
            _lopMonHocReportService = lopMonHocReportService;
            _thamSoService = thamSoService;
        }

        public bool ExportToExcelWithTemplate<T>(ref MemoryStream templateFileStream, List<TemplateToken> tokenList, List<T> dataObjectList, uint startRowIndex)
        {
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(templateFileStream, true))
            {
                var sheet = myWorkbook.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().First();
                WorksheetPart workSheetPart = (WorksheetPart)myWorkbook.WorkbookPart.GetPartById(sheet.Id.Value);

                SheetData sheetData = workSheetPart.Worksheet.Elements<SheetData>().First();

                sheetData = CreateSheetData<T>(sheetData,dataObjectList,tokenList,(int)startRowIndex);
                workSheetPart.Worksheet.Save();
                myWorkbook.Close();
            }
            return true;
        }

        private SheetData CreateSheetData<T>(SheetData oldSheetData, List<T> objectList, List<TemplateToken> tokenList, int startRowIndex)
        {
            var sheetData = oldSheetData;
            int numRows = objectList.Count();
            tokenList = tokenList.Where(token => String.IsNullOrEmpty(token.Header) == false).ToList();

            var header = new Row();
            int index = startRowIndex;
            header.RowIndex = (uint)index;
            var az = new List<Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (char)i).ToArray());

            List<string> fieldNames = GetPropertyInfo<T>();
            foreach (var obj in objectList)
            {
                var row = new Row
                {
                    RowIndex = (uint)index
                };
                foreach (TemplateToken token in tokenList)
                {
                    string fieldName = token.PropertyName;
                    PropertyInfo propertyInfo = obj.GetType().GetProperty(fieldName);
                    if (propertyInfo != null)
                    {
                        var fieldValue = propertyInfo.GetValue(obj, null);
                        var fieldType = fieldValue.GetType();
                        var cell = ExcelCustomCellFactory.AutoCreateCell(fieldValue, token.Header, index);
                        row.Append(cell);
                    }
                }
                sheetData.Append(row);
                index++;
            }
            return sheetData;
        }

        private static List<String> GetPropertyInfo<T>()
        {
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            return propertyInfos.Select(propertyInfo => propertyInfo.Name).ToList();
        }
               

        public IEnumerable<TemplateToken> GetInfoFromTemplateFile(Stream fileStream, IEnumerable<TemplateToken> tokens, out uint startRowIndex)
        {
            var tokenList = tokens.ToList();
            startRowIndex = 0;
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileStream,false))
            {
                var sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().First();
                WorksheetPart workSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);

                SheetData sheetData = workSheetPart.Worksheet.Elements<SheetData>().First();

                IEnumerable<Row> rows = workSheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
                foreach (Row row in rows)
                {
                    var cellArray = row.Elements<Cell>().ToArray();
                    foreach (Cell cell in cellArray)
                    {
                        if (cell.CellValue == null)
                            continue;
                        int sharedStringIndex;
                        if (Int32.TryParse(cell.CellValue.Text, out sharedStringIndex))
                        {
                            SharedStringItem item = GetSharedStringItemById(document.WorkbookPart, sharedStringIndex);
                            if (item != null)
                            {
                                string cellValue = item.InnerText;
                                if (cellValue.StartsWith("{") && cellValue.EndsWith("}"))
                                {
                                    cellValue = cellValue.TrimStart('{').TrimEnd('}').ToLower();
                                    var tokenIndex = tokenList.FindIndex(token => token.TokenName.ToLower() == cellValue);
                                    if (tokenIndex != -1)
                                    {
                                        var templateToken = tokenList.ElementAt(tokenIndex);
                                        templateToken.Header = GetColumnName(cell.CellReference.Value);
                                        startRowIndex = GetRowIndex(cell.CellReference.Value);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return tokenList.AsEnumerable<TemplateToken>();
        }

        public IEnumerable<TemplateToken> GetAllTokenFromTemplate(string templateName)
        {
            List<TemplateToken> tokenList = new List<TemplateToken>();
            string xml = _thamSoService.GetThamSo(templateName);
            XmlDocument document = new XmlDocument();
            document.LoadXml(xml);
            var rootNode = document.FirstChild;
            if (rootNode != null)
            {
                XmlNodeList childNodeList = rootNode.ChildNodes;
                foreach (XmlNode node in childNodeList)
                {
                    TemplateToken token = new TemplateToken();
                    token.TokenName = node.InnerText;
                    token.PropertyName = node.Name;
                    tokenList.Add(token);
                }
            }
            return tokenList;
        }

        private static string GetColumnName(string cellName)
        {
            // Create a regular expression to match the column name portion of the cell name.
            Regex regex = new Regex("[A-Za-z]+");
            Match match = regex.Match(cellName);

            return match.Value;
        }

        private static uint GetRowIndex(string cellName)
        {
            // Create a regular expression to match the row index portion the cell name.
            Regex regex = new Regex(@"\d+");
            Match match = regex.Match(cellName);

            return uint.Parse(match.Value);
        }

        public static SharedStringItem GetSharedStringItemById(WorkbookPart workbookPart, int id)
        {
            try
            {
                return workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
            }
            catch
            {
                return null;
            }
        }

        public IEnumerable<ExcelReadDataModel> ReadExcelDataFile(Stream fileStream, int startRowIndex, int endRowIndex, string indexColumn, string valueColumn)
        {
            IList<ExcelReadDataModel> dataList = new List<ExcelReadDataModel>();

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileStream, false))
            {
                var sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().First();
                WorksheetPart workSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);
                SheetData sheetData = workSheetPart.Worksheet.Elements<SheetData>().First();
                IEnumerable<Row> rows = workSheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
                foreach (Row row in rows)
                {
                    if (row.RowIndex < startRowIndex || row.RowIndex > endRowIndex)
                        continue;
                    ExcelReadDataModel dataModel = new ExcelReadDataModel();
                    var cells = row.Elements<Cell>().ToArray();
                    foreach (Cell cell in cells)
                    {
                        if (GetColumnName(cell.CellReference.Value) == indexColumn)
                        {
                            dataModel.DataEntryIndex = cell.CellValue.Text;
                        }
                        else
                        if (GetColumnName(cell.CellReference.Value) == valueColumn)
                        {
                            dataModel.DataEntryValue = cell.CellValue.Text;
                        }
                    }
                    if (!(String.IsNullOrEmpty(dataModel.DataEntryIndex) || String.IsNullOrEmpty(dataModel.DataEntryValue)))
                    {
                        dataList.Add(dataModel);
                    }
                }
            }
            return dataList;
        }

        public IEnumerable<T> ReadExcelDataFile<T>(Stream fileStream, IList<TemplateToken> tokenList, int startRowIndex)
        {
            IList<T> resultList = new List<T>();
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileStream, false))
            {
                var sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().First();
                WorksheetPart workSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id.Value);
                SheetData sheetData = workSheetPart.Worksheet.Elements<SheetData>().First();
                IEnumerable<Row> rows = workSheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>();
                foreach (Row row in rows)
                {
                    if (row.RowIndex < startRowIndex)
                        continue;
                    var cells = row.Elements<Cell>().ToArray();
                    T obj = Activator.CreateInstance<T>();
                    foreach (Cell cell in cells)
                    {
                        var columnName = GetColumnName(cell.CellReference.Value);
                        foreach (var token in tokenList)
                        {
                            if (columnName == token.Header)
                            {
                                PropertyInfo propertyInfo = obj.GetType().GetProperty(token.PropertyName);
                                int id;
                                if (cell.CellValue != null && Int32.TryParse(cell.CellValue.Text, out id))
                                {
                                    var item = GetSharedStringItemById(document.WorkbookPart, id);
                                    if (item != null)
                                    {
                                        propertyInfo.SetValue(obj, item.InnerText, null);
                                    }
                                    else
                                    {
                                        var dateTimeContent = DateTimeStringFromJulianDate(id);
                                        if (!String.IsNullOrEmpty(dateTimeContent))
                                        {
                                            propertyInfo.SetValue(obj, dateTimeContent, null);
                                        }
                                    }
                                }
                            }
                        }

                    }
                    resultList.Add(obj);
                }
            }

            return resultList;
        }

        public void ReadFile()
        {
            throw new NotImplementedException();
        }

        public string DateTimeStringFromJulianDate(int julianDate)
        {
            int RealJulian = julianDate + 1900000;
            int Year = Convert.ToInt32(RealJulian.ToString().Substring(0, 4));
            int DoY = Convert.ToInt32(RealJulian.ToString().Substring(4));
            DateTime dtOut = new DateTime(Year, 1, 1);
            return dtOut.AddDays(DoY - 1).ToShortDateString();
        }
    }
}
